package db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;

public class ComputeSales {

	public static Vector<Vector<String>> salesBetween(String begin, String end) {
		float gst=Float.parseFloat(SalesConfig.getGST());
		String sqlString = "SELECT ef.id, COUNT(mt.flight_id) sold,EF.seats total,EF.PRICE,ef.max_discount DISCOUNT ,"
				+

				" count(mt.flight_id)*ea.tax AirportTax,"
				+ " round(ifnull(sum(mt.price*"+gst+"),0),2) GST,"
				+

				" round(ifnull(sum(MT.price)*"+(1-gst)+"-count(mt.flight_id)*ea.tax,0),2) NetValue,round(ifnull(sum(mt.price),0),2)"
				+ ",round(COUNT(mt.flight_id)/EF.seats*100,2) FROM flights EF LEFT JOIN booked MT"
				+ " ON mt.flight_id=ef.id AND ef.start_date>='"
				+ begin
				+ "' AND ef.start_date<='"
				+ end
				+ "'  left join cities ea on ef.origin=ea.id"
				+ " GROUP BY ef.id;";
		System.out.println(sqlString);
		Vector<Vector<String>> ret = new Vector<Vector<String>>();
		ResultSet resultSet;
		try {
			resultSet = DataBaseManager.getConnection().createStatement()
					.executeQuery(sqlString);

			while (resultSet.next()) {
				Vector<String> t = new Vector<String>(9);
				for (int i = 0; i <10; i++)
					t.add(resultSet.getString(i+1));
				ret.add(t);
					

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
}
